Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant s columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is laborious. The process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerized replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a contiguous set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output.
Cell ranges are used to automate computations in spreadsheets. Whether cells or cell ranges are named or not, they can be referenced within a formula either by a “relative” or an “absolute” reference. Such a reference can be the address of the referenced cell range, or the name of the referenced cell range if it turns that this cell range is named.
It is common to find in electronic spreadsheet based applications some large tables which are organised according to a structured way. This structure typically results in organising rows, columns and sheets in such a way that the content of each cell within a given column and within a given sheet can be obtained as the result of a copy-paste operation where the source copied cell is the top cell within this same column and same sheet. In such typical situations, this “reference” cell can contain a formula referencing in a relative or absolute way one or several other cells, so that each of the other cells within the same column of the same sheet will also contain the same formula where the absolute references will be kept unchanged and where the relative references will point to other relative cells.
Such a typical situation is illustrated in FIG. 3A where a table is used to compute a sales item price according to some input data. In this table, the content of the cell with address C6 (column entitled “Unit Cost”) is for instance equal to formula “@CostOf(B6)” where @CostOf is a dedicated function providing the cost of an item used as parameter. In the same table, the content of the cell with address G6 (column entitled “Exchange rate”) is for instance equal to formula “@RateOf(F6)” where @RateOf is a dedicated function returning the exchange rate for a currency passed as parameter. In the same table, the content of the cell with address I6 (column entitled “Price”) is for instance equal to formula “C6*D6*G6/(1−$PROFIT)” where “PROFIT” is the name given to the cell range with address I3 where the profit figure is recorded. The content of each cell within the “Unit Cost” table can be obtained by copy-pasting the reference cell with address C6, so that the content of the cell with address Cx (where x takes the values 7 to 10) is equal to “@CostOf(Bx)”. Similarly, the content of the cells with address Gx and with address Ix are obtained by copy-pasting the content of the reference cells with address G6 and with address I6, respectively. The content is equal respectively to “@RateOf(Fx)” and to “Cx*Dx*Gx/(1−$PROFIT)”. The copy-paste operation is thus a powerful tool for copying in many different cells or ranges of cells, the content of a reference cell or of a reference range of cells. Nevertheless this copy-paste operation presents some limitations, as outlined hereafter.
Assume that in our example the content of a reference cell needs to be updated to reflect some structural change of the table it belongs to. This structural change is illustrated in FIG. 3B where the profit parameter (used to derive a price from a cost) is no longer constant for all sold items (as shown in FIG. 3A with the cell of address I3, and named “PROFIT”), but depends on the sold item itself, as represented in the table by the cells within the column entitled “Profit”. Under this new rule, the content of the cell with address I6 (within the column entitled “Price”) is now equal to formula “C6*D6*G6/(1−H6)”. As the reference cell I6 for the “Price” column has been changed, reflecting the table structural update, it is necessary to reapply the copy-paste operation from this reference cell to all the other cells following the same logic, that is the cells with address I7 to I10 as shown in FIG. 3B. More generally, this operation must be carefully done each time a reference range of cells content is updated and must be applied to every range of cells whose content has been initially derived from the content of the reference range of cells through a copy-paste operation.
With large and complex spreadsheets, such an operation may take quite a long time and is error prone because the spreadsheet user may miss some of the ranges of cells where the copy-paste operation must be reapplied. When this happens, the resulting spreadsheet may provide erroneous results. The present invention offer a powerful and efficient solution to this problem by defining a method and a system for persistently performing a copy-paste operation between a reference range of cells and one or more destination ranges of cells.